﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using System.Data;
using System.Data.SqlClient;
using DAL.Entities;

namespace DAL.Entities_DAL
{
    public class Canho_DAL: BaseDAL, UTL.IBaseDAL
    {
        public int Count()
        {
            throw new NotImplementedException();
        }

        public DataTable Select()
        {
            var sql = @" select * from CAN_HO";
            return ExecuteQuery(sql);
        }

        public DataTable Select(object obj)
        {
            throw new NotImplementedException();
        }

        public object GetByKey(object key)
        {
            throw new NotImplementedException();
        }

        public bool Delete(string id)
        {
            string sql = @"DELETE FROM CAN_HO WHERE MA_CH = '{0}'";
            sql = string.Format(sql, id);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Insert(object obj)
        {
            var o = (Canho)obj;
            string sql = @" INSERT INTO 
                            CAN_HO(MA_CH, DIEN_TICH, TRANG_THAI, SO_LUONG_XE_HOI, SO_LUONG_XE_MAY, SO_LUONG_XE_DAP, LO, MA_CC)
                            VALUES( '{0}', '{1}', N'{2}','{3}','{4}','{5}',N'{6}','{7}')
                          ";
            sql = string.Format(sql, o.MA_CH, o.DIEN_TICH, o.TRANG_THAI, o.SO_LUONG_XE_HOI, o.SO_LUONG_XE_MAY, o.SO_LUONG_XE_DAP, o.LO, o.MA_CC);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public bool Update(object obj)
        {
            var o = (Canho)obj;
            string sql = @" 
                            UPDATE  
                            CAN_HO SET DIEN_TICH = '{1}', TRANG_THAI = N'{2}', SO_LUONG_XE_HOI = '{3}', SO_LUONG_XE_MAY = '{4}', SO_LUONG_XE_DAP = '{5}', LO = N'{6}', MA_CC = '{7}'
                            WHERE MA_CH = '{0}'
                          ";
            sql = string.Format(sql, o.MA_CH, o.DIEN_TICH, o.TRANG_THAI, o.SO_LUONG_XE_HOI, o.SO_LUONG_XE_MAY, o.SO_LUONG_XE_DAP, o.LO, o.MA_CC);
            return ExecuteNonQuery(sql) > 0 ? true : false;
        }

        public DataTable Search(string name)
        {
            throw new NotImplementedException();
        }

        //tim ma so lon nhat

        public DataTable Select_max()
        {
            var sql = @"SELECT MAX(MA_CH) as id FROM CAN_HO";
            return ExecuteQuery(sql);
        }

        //dem so khach hang

        public string Select_count()
        {
            string sql = @"SELECT count(MA_CH) as id FROM CAN_HO";
            var tb = ExecuteQuery(sql);
            return tb.Rows.Count > 0 ? tb.Rows[0]["id"] + "" : "0";
        }

        //load data len combobox chung cu

        public DataTable Select_Chungcu()
        {
            var sql = @"SELECT MA_CC as id, TEN_CC AS ten FROM CHUNG_CU";
            return ExecuteQuery(sql);
        }

        //load data can ho

        public DataTable load_can_ho()
        {
            var sql = @"SELECT MA_CH FROM CAN_HO";
            return ExecuteQuery(sql);
        }

        // load can ho chua duoc thue

        public DataTable load_can_ho_khong_thue()
        {
            var sql = @"SELECT MA_CH as id FROM CAN_HO where MA_CH not in (select ma_ch from KH_CH)";
            return ExecuteQuery(sql);
        }
        //tim kiem can ho theo ma can ho

        public DataTable tk_canho(string mach)
        {
            try
            {
                var sql = @" 
                            select *
                            from can_ho
                            where ma_ch ='{0}'                            
                            ";
                sql = string.Format(sql, mach);
                return ExecuteQuery(sql);
            }
            catch
            {
                return null;
            }
        }
    }
}
